# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql as pq                # 导入pymysql
from books.utils import outputInfo

from dotenv import dotenv_values

envs = dotenv_values('../.env')


class BooksPipeline:
    def process_item(self, item, spider):
        return item


class MySQLPipeline(object):
    def __init__(self):
        self.conn = pq.connect(host='localhost', user=envs['DB_USERNAME'],
                               passwd=envs['DB_PASSWORD'],
                               db=envs['DB_DATABASE'], charset='utf8')
        self.cur = self.conn.cursor()

    def process_item(self, item, spider):
        spider.count += 1
        if spider.count % 100 == 0:
            outputInfo('progress', spider.count, spider)

        # 首先插入j_d_books记录
        sql = "insert into d_d_books(dd_price, dd_id, isbn, job, comment_count, average_score, \
            created_at, updated_at \
            ) VALUES (%s, %s, %s, %s, %s, %s, \
            NOW(), NOW())"
        self.cur.execute(
            sql, (
                item["price"],
                item["id"],
                item["ISBN"],
                spider.jobID,
                item["comment_count"],
                item["average_score"],
            )
        )

        infoColumns = [
            'author',
            'publisher',
            'name',
            'cover',
            'digest',
            'publish_date',
        ]

        sql = "insert ignore into books("+','.join(infoColumns)+", isbn, \
            created_at, updated_at \
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), NOW())"
        self.cur.execute(
            sql, [item[key] for key in infoColumns] + [item["ISBN"]]
        )

        for key in infoColumns:
            if item[key]:
                if key == 'publish_date':
                    sql = "update books set " + key + \
                        "=%s where isbn=%s and isnull("+key+")"
                elif key == 'name':
                    # 名称要更短的
                    sql = "update books set name=%s where isbn=%s and (isnull(name) or char_length(name)>"+str(len(
                        item[key]))+")"
                elif key == 'author' and len(item[key]) > 1:
                    # 作者要更长的 换成更短的试试呢？
                    sql = "update books set author=%s where isbn=%s and isnull(author)"
                else:
                    sql = "update books set " + key + \
                        "=%s where isbn=%s and (isnull("+key+") or "+key+"='')"
                self.cur.execute(
                    sql, [item[key], item['ISBN']]
                )

        self.conn.commit()
        return item

    def close_spider(self, spider):
        self.cur.close()
        self.conn.close()


class CleanPipeline:

    def process_item(self, item, spider):

        return {
            "author": (item["author"] or '')[:200],
            "publisher": (item["publisher"] or '')[:200],
            "name": (item["name"] or '')[:200],
            "price": item["price"] or '',
            "cover": (item["cover"] or '')[:200],
            "id": (item["id"] or '')[:20],
            "digest": (item["digest"] or '')[:5000],
            "publish_date": item["publishDate"] or None,  # 转换
            "ISBN": item["ISBN"][:20],
            "comment_count": int(item['CommentCount']),
            "average_score": int(item['AverageScore']),
        }
